Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Interpreting SQL Trace

This section looks at some of the statistics available from SQL Trace and how to interpret them. For each SQL statement executed, SQL Trace provides the following information:


Parameter Description

count Number of times the OCI procedure was executed. (The OCI interface is the standard set of calls used to access the Oracle database.)
cpu CPU time in seconds executing. This value is the amount of time Oracle used to process the statement.
elapsed Elapsed time in seconds executing. This value is equivalent to the user’s response time.
disk Number of physical reads of buffers from disk. This value tells you how many reads actually missed the buffer cache and had to go to physical disk.
query Number of buffers gotten for consistent read. This value represents the number of buffers retrieved in consistent mode. Consistent mode guarantees consistent reads throughout the transaction; it is used for most queries.
current Number of buffers gotten in current mode (usually for update). In current mode, the data blocks gotten reflect the value at that instant in time.
rows Number of rows processed by the fetch or execute call. This value gives you an idea of how many instructions have been executed.

By looking at each of these parameters, you can get an idea of how your SQL statements are being processed and which statements are taking the most time. By analyzing which statements are taking the longest, you may be able to find some inefficiencies you can correct.

The SQL Trace facility was enabled in a session by using this Oracle command:

EXECUTE RDBMS_system.set_sql_trace_in_session(7,3,TRUE);

In another session (with SID = 7 and Serial# = 3), the following SQL statement was executed:

SELECT
   SUBSTR(dogname,1,20) "Dog Name",
      SUBSTR(description,1,20) "Breed",
    SUBSTR(owner,1,20) "Owner"
FROM
    dogs, breeds
WHERE
    dogs.breed = breeds.breed
ORDER BY
    dogs.breed;

The SQL Trace facility was later disabled using this Oracle command from the fist session:

EXECUTE  RDBMS_system.set_sql_trace_in_session(7,3,FALSE);

Following the execution of the SQL statements, the trace file was translated by running TKPROF as follows:

tkprof   orclshad.trc   trace.out   sys=no   explain=ed/ed

In this syntax, the following are true:

orclshad.trc Trace file generated by SQL Trace
trace.out Where I want the output to go
sys=no Indicates that no SYS or recursive SQL statements should be printed
explain=ed/ed Specifies that I also want to generate EXPLAIN PLAN output

TKPROF generated the output file shown in Listing 25.1.

Listing 25.1 The TKPROF Output File for a Sample Trace

TKPROF: Release 7.2.2.3.0 - Beta on Sun Nov 26 13:03:20 1995

Copyright Oracle Corporation 1979, 1994. All rights reserved.

Trace file: orclshad.trc
Sort options: default

******************************************************************************
count    = number of times OCI procedure was executed
cpu       = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk      = number of physical reads of buffers from disk
query     = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows      = number of rows processed by the fetch or execute call
******************************************************************************

SELECT
    SUBSTR(dogname,1,20) "Dog Name",
      SUBSTR(breed_name,1,20) "Breed",
     SUBSTR(owner,1,20) "Owner"
FROM
    dogs, breeds
WHERE
    dogs.breed = breeds.breed
ORDER BY
    dogs.breed

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.57          2          0          4           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.08          2          2          6          25
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.65          4          2         10          25

Misses in library cache during parse: 1
Optimizer hint: CHOOSE
Parsing user id: 10 (ED)

  Rows   Execution Plan
 ------  --------------------------------------------------
     0  SELECT STATEMENT   HINT: CHOOSE
     60   MERGE JOIN
      0    SORT (JOIN)
     0    TABLE ACCESS (FULL) OF ‘BREEDS’
      0    SORT (JOIN)
     0    TABLE ACCESS (FULL) OF ‘DOGS’

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.57          2          0          4           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.08          2          2          6          25
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.65          4          2         10          25

Misses in library cache during parse: 1

     1  user  SQL statements in session.
      8  internal SQL statements in session.
     9  SQL statements in session.
      1  statement EXPLAINed in this session.
********************************************************************************
Trace file: orclshad.trc
Trace file compatibilty: 7.02.01
Sort options: default

        1  session in tracefile.
       1  user  SQL statements in trace file.
        8  internal SQL statements in trace file.
       9  SQL statements in trace file.
        6  unique SQL statements in trace file.
       1  SQL statements EXPLAINed using schema:
           ED.prof$plan_table
             Default table was used.
            Table was created.
           Table was dropped.
     109  lines in trace file.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.